"""
数据库查询优化工具
提供常用的查询优化方法和分页功能
"""

from typing import Type, TypeVar, Generic, Optional, List, Dict, Any
from sqlalchemy.orm import Session, Query, selectinload, joinedload
from sqlalchemy.ext.declarative import DeclarativeMeta
from sqlalchemy import func, text
from pydantic import BaseModel
from math import ceil

from app.core.logging import get_logger

logger = get_logger(__name__)

ModelType = TypeVar("ModelType", bound=DeclarativeMeta)


class PaginationResult(BaseModel, Generic[ModelType]):
    """分页结果模型"""
    items: List[ModelType]
    total: int
    page: int
    size: int
    pages: int
    has_next: bool
    has_prev: bool
    
    model_config = {"arbitrary_types_allowed": True}


class QueryOptimizer:
    """查询优化器"""
    
    @staticmethod
    def paginate(
        query: Query,
        page: int = 1,
        size: int = 20,
        max_size: int = 100
    ) -> PaginationResult:
        """
        分页查询
        
        Args:
            query: SQLAlchemy查询对象
            page: 页码（从1开始）
            size: 每页大小
            max_size: 最大每页大小
            
        Returns:
            PaginationResult: 分页结果
        """
        # 参数验证
        page = max(1, page)
        size = min(max(1, size), max_size)
        
        # 计算总数
        total = query.count()
        
        # 计算分页信息
        pages = ceil(total / size) if total > 0 else 0
        has_next = page < pages
        has_prev = page > 1
        
        # 获取当前页数据
        offset = (page - 1) * size
        items = query.offset(offset).limit(size).all()
        
        return PaginationResult(
            items=items,
            total=total,
            page=page,
            size=size,
            pages=pages,
            has_next=has_next,
            has_prev=has_prev
        )
    
    @staticmethod
    def optimize_query_with_relations(
        query: Query,
        relations: List[str],
        strategy: str = "selectin"
    ) -> Query:
        """
        优化包含关联关系的查询
        
        Args:
            query: 基础查询
            relations: 关联关系列表
            strategy: 加载策略 ('selectin' 或 'joined')
            
        Returns:
            Query: 优化后的查询
        """
        if not relations:
            return query
            
        if strategy == "selectin":
            # 使用selectinload避免N+1查询
            for relation in relations:
                query = query.options(selectinload(relation))
        elif strategy == "joined":
            # 使用joinedload进行连接查询
            for relation in relations:
                query = query.options(joinedload(relation))
        
        return query
    
    @staticmethod
    def add_filters(
        query: Query,
        model: Type[ModelType],
        filters: Dict[str, Any]
    ) -> Query:
        """
        动态添加过滤条件
        
        Args:
            query: 基础查询
            model: 模型类
            filters: 过滤条件字典
            
        Returns:
            Query: 添加过滤条件后的查询
        """
        for field, value in filters.items():
            if value is None:
                continue
                
            if hasattr(model, field):
                column = getattr(model, field)
                
                # 处理不同类型的过滤条件
                if isinstance(value, str):
                    if value.startswith('%') or value.endswith('%'):
                        # 模糊查询
                        query = query.filter(column.like(value))
                    else:
                        # 精确查询
                        query = query.filter(column == value)
                elif isinstance(value, list):
                    # IN查询
                    query = query.filter(column.in_(value))
                elif isinstance(value, dict):
                    # 范围查询
                    if 'gte' in value:
                        query = query.filter(column >= value['gte'])
                    if 'lte' in value:
                        query = query.filter(column <= value['lte'])
                    if 'gt' in value:
                        query = query.filter(column > value['gt'])
                    if 'lt' in value:
                        query = query.filter(column < value['lt'])
                else:
                    # 普通等值查询
                    query = query.filter(column == value)
        
        return query
    
    @staticmethod
    def add_sorting(
        query: Query,
        model: Type[ModelType],
        sort_by: Optional[str] = None,
        sort_order: str = "asc"
    ) -> Query:
        """
        添加排序
        
        Args:
            query: 基础查询
            model: 模型类
            sort_by: 排序字段
            sort_order: 排序方向 ('asc' 或 'desc')
            
        Returns:
            Query: 添加排序后的查询
        """
        if not sort_by or not hasattr(model, sort_by):
            return query
            
        column = getattr(model, sort_by)
        
        if sort_order.lower() == "desc":
            query = query.order_by(column.desc())
        else:
            query = query.order_by(column.asc())
            
        return query


class DatabaseMetrics:
    """数据库性能监控"""
    
    @staticmethod
    def log_slow_query(query_str: str, execution_time: float, threshold: float = 1.0):
        """记录慢查询"""
        if execution_time > threshold:
            logger.warning(
                "慢查询检测",
                query=query_str,
                execution_time=execution_time,
                threshold=threshold
            )
    
    @staticmethod
    def get_connection_pool_status(engine) -> Dict[str, Any]:
        """获取连接池状态"""
        pool = engine.pool
        return {
            "size": pool.size(),
            "checked_in": pool.checkedin(),
            "checked_out": pool.checkedout(),
            "overflow": pool.overflow(),
            "invalid": pool.invalid()
        }


class CacheHelper:
    """查询缓存辅助类"""
    
    @staticmethod
    def generate_cache_key(
        model_name: str,
        method: str,
        **kwargs
    ) -> str:
        """生成缓存键"""
        import hashlib
        import json
        
        # 创建缓存键的基础部分
        key_parts = [model_name, method]
        
        # 添加参数
        if kwargs:
            # 排序参数以确保一致性
            sorted_kwargs = json.dumps(kwargs, sort_keys=True, default=str)
            key_parts.append(hashlib.md5(sorted_kwargs.encode()).hexdigest())
        
        return ":".join(key_parts)


def create_optimized_query(
    db: Session,
    model: Type[ModelType],
    filters: Optional[Dict[str, Any]] = None,
    relations: Optional[List[str]] = None,
    sort_by: Optional[str] = None,
    sort_order: str = "asc",
    page: Optional[int] = None,
    size: Optional[int] = None
):
    """
    创建优化的查询
    
    Args:
        db: 数据库会话
        model: 模型类
        filters: 过滤条件
        relations: 关联关系
        sort_by: 排序字段
        sort_order: 排序方向
        page: 页码
        size: 每页大小
        
    Returns:
        查询结果或分页结果
    """
    query = db.query(model)
    
    # 添加过滤条件
    if filters:
        query = QueryOptimizer.add_filters(query, model, filters)
    
    # 优化关联查询
    if relations:
        query = QueryOptimizer.optimize_query_with_relations(query, relations)
    
    # 添加排序
    query = QueryOptimizer.add_sorting(query, model, sort_by, sort_order)
    
    # 分页查询
    if page is not None and size is not None:
        return QueryOptimizer.paginate(query, page, size)
    
    return query.all()


# 装饰器：查询性能监控
def monitor_query_performance(threshold: float = 1.0):
    """查询性能监控装饰器"""
    def decorator(func):
        import time
        from functools import wraps
        
        @wraps(func)
        def wrapper(*args, **kwargs):
            start_time = time.time()
            try:
                result = func(*args, **kwargs)
                return result
            finally:
                execution_time = time.time() - start_time
                DatabaseMetrics.log_slow_query(
                    query_str=func.__name__,
                    execution_time=execution_time,
                    threshold=threshold
                )
        return wrapper
    return decorator